#!/usr/bin/env python3
"""
数据库迁移脚本：将文章ID从整数改为20位随机字符串
"""
import sqlite3
import secrets
import string
import os

def generate_post_id():
    """生成20位随机字符串ID"""
    characters = string.ascii_letters + string.digits
    return ''.join(secrets.choice(characters) for _ in range(20))

def migrate_database():
    """执行数据库迁移"""
    db_path = "blog.db"
    
    if not os.path.exists(db_path):
        print(f"数据库文件 {db_path} 不存在")
        return
    
    # 备份原数据库
    backup_path = f"{db_path}.backup"
    if os.path.exists(backup_path):
        os.remove(backup_path)
    os.rename(db_path, backup_path)
    print(f"已备份原数据库到 {backup_path}")
    
    # 连接备份数据库
    conn = sqlite3.connect(backup_path)
    cursor = conn.cursor()
    
    # 获取所有文章数据
    cursor.execute("SELECT * FROM posts")
    posts = cursor.fetchall()
    
    # 获取所有评论数据
    cursor.execute("SELECT * FROM comments")
    comments = cursor.fetchall()
    
    # 获取所有post_tags数据
    cursor.execute("SELECT * FROM post_tags")
    post_tags = cursor.fetchall()
    
    conn.close()
    
    # 创建新的数据库
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    
    # 首先复制所有其他表（用户、分类、标签）
    cursor.execute("ATTACH DATABASE ? AS backup", (backup_path,))
    
    # 复制用户表
    cursor.execute("CREATE TABLE users AS SELECT * FROM backup.users")
    
    # 复制分类表
    cursor.execute("CREATE TABLE categories AS SELECT * FROM backup.categories")
    
    # 复制标签表
    cursor.execute("CREATE TABLE tags AS SELECT * FROM backup.tags")
    
    # 创建新的posts表
    cursor.execute("""
        CREATE TABLE posts (
            id VARCHAR(20) PRIMARY KEY,
            title VARCHAR(200) NOT NULL,
            content TEXT NOT NULL,
            excerpt TEXT,
            cover_image VARCHAR(255),
            is_published BOOLEAN DEFAULT 0,
            is_featured BOOLEAN DEFAULT 0,
            view_count INTEGER DEFAULT 0,
            like_count INTEGER DEFAULT 0,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME,
            published_at DATETIME,
            author_id INTEGER NOT NULL,
            category_id INTEGER,
            FOREIGN KEY (author_id) REFERENCES users (id),
            FOREIGN KEY (category_id) REFERENCES categories (id)
        )
    """)
    
    # 创建新的post_tags表
    cursor.execute("""
        CREATE TABLE post_tags (
            post_id VARCHAR(20),
            tag_id INTEGER,
            PRIMARY KEY (post_id, tag_id),
            FOREIGN KEY (post_id) REFERENCES posts (id),
            FOREIGN KEY (tag_id) REFERENCES tags (id)
        )
    """)
    
    # 创建新的comments表
    cursor.execute("""
        CREATE TABLE comments (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            content TEXT NOT NULL,
            is_approved BOOLEAN DEFAULT 1,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME,
            author_id INTEGER NOT NULL,
            post_id VARCHAR(20) NOT NULL,
            parent_id INTEGER,
            FOREIGN KEY (author_id) REFERENCES users (id),
            FOREIGN KEY (post_id) REFERENCES posts (id),
            FOREIGN KEY (parent_id) REFERENCES comments (id)
        )
    """)
    
    # 创建ID映射表
    id_mapping = {}
    
    # 迁移文章数据
    print("正在迁移文章数据...")
    for post in posts:
        old_id = post[0]  # 原ID
        new_id = generate_post_id()
        id_mapping[old_id] = new_id
        
        # 插入新文章数据
        cursor.execute("""
            INSERT INTO posts (id, title, content, excerpt, cover_image, is_published, 
                             is_featured, view_count, like_count, created_at, updated_at, 
                             published_at, author_id, category_id)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """, (new_id, post[1], post[2], post[3], post[4], post[5], post[6], 
              post[7], post[8], post[9], post[10], post[11], post[12], post[13]))
    
    # 迁移post_tags数据
    print("正在迁移文章标签关联数据...")
    for post_tag in post_tags:
        old_post_id = post_tag[0]
        tag_id = post_tag[1]
        new_post_id = id_mapping.get(old_post_id)
        
        if new_post_id:
            cursor.execute("""
                INSERT INTO post_tags (post_id, tag_id)
                VALUES (?, ?)
            """, (new_post_id, tag_id))
    
    # 迁移评论数据
    print("正在迁移评论数据...")
    for comment in comments:
        old_post_id = comment[6]  # post_id字段
        new_post_id = id_mapping.get(old_post_id)
        
        if new_post_id:
            cursor.execute("""
                INSERT INTO comments (id, content, is_approved, created_at, updated_at,
                                    author_id, post_id, parent_id)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?)
            """, (comment[0], comment[1], comment[2], comment[3], comment[4],
                  comment[5], new_post_id, comment[7]))
    
    conn.commit()
    conn.close()
    
    print(f"迁移完成！")
    print(f"共迁移了 {len(posts)} 篇文章")
    print(f"共迁移了 {len(comments)} 条评论")
    print(f"共迁移了 {len(post_tags)} 个文章标签关联")
    print(f"原数据库已备份到 {backup_path}")

if __name__ == "__main__":
    migrate_database()
